using Dapper;
using Lzfy_His_Service.Models;
using Lzfy_His_Service.Services;
using System.Collections.Generic;
using System.Threading.Tasks;
using System;
using System.Text;
using Microsoft.Extensions.Logging;
using Lzfy_His_Service.Utilities;

namespace Lzfy_His_Service.Repositories
{
    public class RefundMasterRepository : IRefundMasterRepository
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly ILogger<RefundMasterRepository> _logger;

        public RefundMasterRepository(
            IDbConnectionFactory connectionFactory,
            ILogger<RefundMasterRepository> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        public async Task<IEnumerable<RefundMaster>> GetAllAsync()
        {
            var stopwatch = System.Diagnostics.Stopwatch.StartNew();
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                var sql = @"SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    patient_id AS PatientId,
                    pat_name AS PatName,
                    id_number AS IdNumber,
                    phone_number AS PhoneNumber,
                    refund_bank_name AS RefundBankName,
                    refund_bank_card_no AS RefundBankCardNo,
                    refund_bank_open_name AS RefundBankOpenName,
                    refund_bank_city AS RefundBankCity,
                    refund_amount AS RefundAmount,
                    public_agent_name AS PublicAgentName,
                    public_agent_phone_number AS PublicAgentPhoneNumber,
                    public_agent_relation AS PublicAgentRelation,
                    public_agent_relation_id AS PublicAgentRelationId,
                    public_agent_refund_bank_name AS PublicAgentRefundBankName,
                    public_agent_refund_bank_card_no AS PublicAgentRefundBankCardNo,
                    public_agent_refund_bank_open_name AS PublicAgentRefundBankOpenName,
                    public_agent_refund_bank_city AS PublicAgentRefundBankCity,
                    audit_user_id AS AuditUserId,
                    audit_user_name AS AuditUserName,
                    audit_result AS AuditResult,
                    audit_date AS AuditDate,
                    refund_type_name AS RefundTypeName,
                    refund_type_id AS RefundTypeId,
                    audit_comment AS AuditComment,
                    his_refund_status AS HisRefundStatus,
                    acct_id AS AcctId,
                    bank_card_holder AS BankCardHolder,
                    refund_balance AS RefundBalance,
                    refund_balance_sn AS RefundBalanceSn,
                    rel_id AS RelId
                    FROM lzfy_refund_master ORDER BY input_date DESC";
                
                var result = await connection.QueryAsync<RefundMaster>(sql);
                DatabaseExceptionLogger.LogDatabasePerformance(_logger, "GetAllRefundMasters", stopwatch.Elapsed, result.Count());
                return result;
            }
            catch (Exception ex)
            {
                DatabaseExceptionLogger.LogDatabaseException(_logger, ex, "GetAllRefundMasters");
                throw;
            }
        }

        public async Task<(IEnumerable<RefundMaster> Data, int TotalCount)> GetAllPagedAsync(int pageIndex = 1, int pageSize = 100)
        {
            var stopwatch = System.Diagnostics.Stopwatch.StartNew();
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                
                // 获取总数
                var countSql = "SELECT COUNT(*) FROM lzfy_refund_master";
                var totalCount = await connection.ExecuteScalarAsync<int>(countSql);
                
                // 获取分页数据
                var sql = @"SELECT 
                    id AS Id,
                    input_date AS InputDate,
                    patient_id AS PatientId,
                    pat_name AS PatName,
                    id_number AS IdNumber,
                    phone_number AS PhoneNumber,
                    refund_bank_name AS RefundBankName,
                    refund_bank_card_no AS RefundBankCardNo,
                    refund_bank_open_name AS RefundBankOpenName,
                    refund_bank_city AS RefundBankCity,
                    refund_amount AS RefundAmount,
                    public_agent_name AS PublicAgentName,
                    public_agent_phone_number AS PublicAgentPhoneNumber,
                    public_agent_relation AS PublicAgentRelation,
                    public_agent_relation_id AS PublicAgentRelationId,
                    public_agent_refund_bank_name AS PublicAgentRefundBankName,
                    public_agent_refund_bank_card_no AS PublicAgentRefundBankCardNo,
                    public_agent_refund_bank_open_name AS PublicAgentRefundBankOpenName,
                    public_agent_refund_bank_city AS PublicAgentRefundBankCity,
                    audit_user_id AS AuditUserId,
                    audit_user_name AS AuditUserName,
                    audit_result AS AuditResult,
                    audit_date AS AuditDate,
                    refund_type_name AS RefundTypeName,
                    refund_type_id AS RefundTypeId,
                    audit_comment AS AuditComment,
                    his_refund_status AS HisRefundStatus,
                    acct_id AS AcctId,
                    bank_card_holder AS BankCardHolder,
                    refund_balance AS RefundBalance,
                    refund_balance_sn AS RefundBalanceSn,
                    rel_id AS RelId
                    FROM lzfy_refund_master 
                    ORDER BY input_date DESC 
                    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
                
                var offset = (pageIndex - 1) * pageSize;
                var result = await connection.QueryAsync<RefundMaster>(sql, new { PageSize = pageSize, Offset = offset });
                
                DatabaseExceptionLogger.LogDatabasePerformance(_logger, "GetAllRefundMastersPaged", stopwatch.Elapsed, result.Count());
                return (result, totalCount);
            }
            catch (Exception ex)
            {
                DatabaseExceptionLogger.LogDatabaseException(_logger, ex, "GetAllRefundMastersPaged");
                throw;
            }
        }
    }
}